<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> 
<html><head> 
  <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
  <title>sqlalchemy (web.py)</title> 
  <link rel="stylesheet" type="text/css" href="/static/webpy-new.css"/> 
  <link rel="SHORTCUT ICON" href="/static/favicon.ico"/> 
</head><body> 
 
        <div id="header"><table class="header"><tr>
                <td class="logo"><a href="/" title="Home"><img src="/static/webpy.gif" alt="" /></a></td> 
<td class="blurb">&ldquo;Think about the ideal way to write a web app.<br />Write the code to make it happen.&rdquo; [<a href="/philosophy">more...</a>]</td> </tr>
</table>
        </div> 
        <div id="location"> 
                <a href="/">home</a> &gt; sqlalchemy
        </div> 
<div id="container"> 
<div id="sidebar">
<strong>get started:</strong>
<ul class="body">
  <li><a href="/install">install</a></li>
  <li><a href="/docs/0.3/tutorial">tutorial</a></li>
</ul>
<strong>learn more:</strong>
<ul class="body">
<li><a href="/docs/0.3/api">api reference</a></li>
<li><a href="/cookbook/">cookbook</a></li>
<li><a href="/src/">code examples</a></li>
</ul>

<strong>dive in:</strong>
<ul class="body"> 
<li><a href="https://github.com/webpy/webpy">source code</a></li>
<li><a href="http://groups.google.com/group/webpy/">mailing list</a></li>
<li><a href="https://github.com/webpy/webpy/issues">issue tracker</a></li>
</ul>
</div>

<div id="main"> 
<div id="content" class="body"> 
<h1>sqlalchemy</h1>

<h2>问题</h2>

<p>如何在web.py中使用sqlalchemy</p>

<h2>方案</h2>

<p>创建一个钩子并使用sqlalchemy的scoped session
(http://www.sqlalchemy.org/docs/05/session.html#unitofwork_contextual)</p>

<pre><code>import string
import random
import web

from sqlalchemy.orm import scoped_session, sessionmaker
from models import *

urls = (
    "/", "add",
    "/view", "view"
)

def load_sqla(handler):
    web.ctx.orm = scoped_session(sessionmaker(bind=engine))
    try:
        return handler()
    except web.HTTPError:
       web.ctx.orm.commit()
       raise
    except:
        web.ctx.orm.rollback()
        raise
    finally:
        web.ctx.orm.commit()


app = web.application(urls, locals())
app.add_processor(load_sqla)


class add:
    def GET(self):
        web.header('Content-type', 'text/html')
        fname = "".join(random.choice(string.letters) for i in range(4))
        lname = "".join(random.choice(string.letters) for i in range(7))
        u = User(name=fname
                ,fullname=fname + ' ' + lname
                ,password =542)
        web.ctx.orm.add(u)
        return "added:" + web.websafe(str(u)) \
                            + "&lt;br/&gt;" \
                            + '&lt;a href="/view"&gt;view all&lt;/a&gt;'

class view:
    def GET(self):
        web.header('Content-type', 'text/plain')
        return "\n".join(map(str, web.ctx.orm.query(User).all()))


if __name__ == "__main__":
    app.run()
</code></pre>

<h3>models.py</h3>

<pre><code>from sqlalchemy import create_engine
from sqlalchemy import Column, Integer, String

engine = create_engine('sqlite:///mydatabase.db', echo=True)

from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()
class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String)
    fullname = Column(String)
    password = Column(String)

    def __init__(self, name, fullname, password):
        self.name = name
        self.fullname = fullname
        self.password = password

    def __repr__(self):
       return "&lt;User('%s','%s', '%s')&gt;" % (self.name, self.fullname, self.password)


users_table = User.__table__
metadata = Base.metadata


if __name__ == "__main__":
    metadata.create_all(engine)
</code></pre>

<p>在跑程序之前,运行'python models.py'来初始化一次数据库.</p>

<div style="clear: both;"></div> 
 
</div> 
 
</div> 
</div>
        <div id="footer"> 
    <p>started by <a style="color: gray;" href="http://www.aaronsw.com/">Aaron Swartz</a> (<a style="color: black;" href="mailto:webpy@aaronsw.com">webpy@aaronsw.com</a>)</p>

<p><a href="https://github.com/webpy/webpy.github.com/blob/master/cookbook/sqlalchemy.zh-cn.md">fork this page on GitHub</a></p>
</div> 
 
</body> 
</html> 
